

// adaptee - 获取DataTable
public class DataRepo
{
    public DataTable Get(string select_sql, string connectionString)
    {
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectionString))
        using (var da = new System.Data.SqlClient.SqlDataAdapter(select_sql, con))
        {
            var dt = new DataTable();

            try
            {
                con.Open();
                da.Fill(dt);
            }
            catch (Exception)
            {
                throw;
            }

            return dt;
        }
    }

}

// adapter - 分页获取数据
public class UserDataRepo
{
    public DataTable GetPageTable(string select_sql, int pageIndex = 1, int pageSize = 15, string orderfield = "date desc")
    {
        int row_from = (pageIndex - 1) * pageSize;
        var page_sql = $"select * from ({select_sql}) as cc order by cc.{orderfield} offset {row_from} row fetch next {pageSize} rows only";
        return Get(page_sql);
    }
}

// client - 分页获取
public class UserOperationCls
{
    var u_data_repo = new UserDataRepo();
    DataTable table = u_data_repo.GetPageTable("select * from Users where isDelete=false and createDate > 2015-1-1", 1, 10, "createDate desc");

    // do something...
}
